/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sv.com.intesal.seguridad.sql;

/**
 *
 * @author JM
 */
public class SQLRoles {

    public static final String insertRol = "INSERT INTO ROLES(codigo_f,id_u,agregar_r,modificar_r,buscar_r,eliminar_r,visible_r,reporte_r)VALUES(?,?,?,?,?,?,?,?)";
    public static final String updateRol = "UPDATE ROLES SET agregar_r=?,modificar_r=?,buscar_r=?,eliminar_r=?,visible_r=?,reporte_r=? where codigo_r=?";
    public static final String updateRol_x_Usuario = "UPDATE ROLES SET agregar_r=?,modificar_r=?,buscar_r=?,eliminar_r=?,visible_r=?,reporte_r=? where codigo_f=? and id_u=?";
    public static final String buscarRoles = "SELECT * FROM FORMULARIO as f,MODULO as m where m.codigo_m=f.codigo_m order by nombre_m";
    public static final String buscarRol = "SELECT * FROM FORMULARIO as f,MODULO as m where m.codigo_m=f.codigo_m where codigo_f=?";
    public static final String getRoles_Usuario = "SELECT m.codigo_m,f.codigo_f,u.usuario_u,u.id_u, m.nombre_m,r.codigo_r,f.nombre_f,f.nombre_form_f,r.agregar_r,r.modificar_r,r.buscar_r,r.eliminar_r,r.visible_r,r.reporte_r FROM ROLES as r,FORMULARIO as f,USUARIO as u,MODULO as m where m.codigo_m=f.codigo_m and f.codigo_f=r.codigo_f and u.id_u=r.id_u and u.id_u=? order by m.nombre_m,f.nombre_f";
    public static final String updateRoles_Modulo = "update roles set agregar_r=?,modificar_r=?,buscar_r=?,eliminar_r=?,visible_r=?,reporte_r=? where codigo_r in ( select r.codigo_r FROM MODULO as m,FORMULARIO as f, ROLES as r  where m.codigo_m=f.codigo_m and f.codigo_f=r.codigo_f and  id_u=? and m.codigo_m=? )";
    public static final String updateRoles_Modulo_All = "update roles set agregar_r=?,modificar_r=?,buscar_r=?,eliminar_r=?,visible_r=?,reporte_r=? where codigo_r in ( select r.codigo_r FROM MODULO as m,FORMULARIO as f, ROLES as r  where m.codigo_m=f.codigo_m and f.codigo_f=r.codigo_f and  id_u=? )";
    public static final String getCountUsuario_x_Formulario = "SELECT count(codigo_r) as count FROM ROLES Where id_u=? and codigo_f=?";
    public static final String insert_Formulario_Usuario_Roles = ""
            + "insert into roles (codigo_f,id_u,agregar_r,modificar_r,eliminar_r,buscar_r,visible_r,reporte_r)"
            + "select f.codigo_f,u.id_u,'NO','NO','NO','NO','NO','NO' from formulario as f,usuario as u where f.codigo_f not in"
            + "( select r.codigo_f from roles as r where r.codigo_f=f.codigo_f and r.id_u=u.id_u)";
    public static final String update_Roles_x_Asiginacion_x_PerfilUsuario = "update roles set  " 
            + " agregar_r=( select agregar_pu from perfil as p, perfil_usuario as pu where p.codigo_p=pu.codigo_p and p.codigo_p=? and roles.codigo_f=pu.codigo_f ) "
            + " , modificar_r=( select modificar_pu from perfil as p, perfil_usuario as pu where p.codigo_p=pu.codigo_p and p.codigo_p=? and roles.codigo_f=pu.codigo_f ) "
            + " , buscar_r=( select buscar_pu from perfil as p, perfil_usuario as pu where p.codigo_p=pu.codigo_p and p.codigo_p=? and roles.codigo_f=pu.codigo_f ) "
            + " , visible_r=( select visible_pu from perfil as p, perfil_usuario as pu where p.codigo_p=pu.codigo_p and p.codigo_p=? and roles.codigo_f=pu.codigo_f ) "
            + " , eliminar_r=( select eliminar_pu from perfil as p, perfil_usuario as pu where p.codigo_p=pu.codigo_p and p.codigo_p=? and roles.codigo_f=pu.codigo_f ) "
            + " , reporte_r=(select reporte_pu from perfil as p, perfil_usuario as pu where p.codigo_p=pu.codigo_p and p.codigo_p=? and roles.codigo_f=pu.codigo_f ) "
            + " where id_u=? ";
}


    /*
update roles set agregar_r=?,modificar_r=?,visible_r,reporte_r=?,buscar_r=?,eliminar_r=? where codigo_r in ( select r.codigo_r FROM MODULO as m,FORMULARIO as f, ROLES as r  where m.codigo_m=f.codigo_m and f.codigo_f=r.codigo_f and  id_u=? and m.codigo_m=? )
     update roles set agregar_r='SI',modificar_r='SI',visible_r,reporte_r='SI',buscar_r='SI',eliminar_r='SI'
where codigo_r in (
select r.codigo_r FROM MODULO as m,FORMULARIO as f, ROLES as r  where m.codigo_m=f.codigo_m and f.codigo_f=r.codigo_f and  id_u=1
)
     */
